package c0907m.group2.aitsm.core.db;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Course_Subject table.
 */
public class CourseSubjectDb extends BaseDb {
    public static final int COURSE = 0;
    public static final int SUBJECT = 1;
    private String insert = "INSERT INTO course_subject VALUES (?, ?)";
    private String delete = "DELETE FROM course_subject WHERE course_id = ?";

    /**
     * Get a list of course_id from a given subject_id and contrary
     *
     * @param id   given id
     * @param type type of id (subject id or course id)
     * @return list of course id (or subject id) depends on type of given id
     * @throws SQLException if a database access error occurs.
     */
    public List<Integer> select(int id, int type) throws SQLException {
        String select = "";
        if (type == COURSE)
            select = "SELECT subject_id FROM course_subject WHERE course_id = " + id;
        if (type == SUBJECT)
            select = "SELECT course_id FROM course_subject WHERE subject_id = " + id;

        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<Integer> list = new ArrayList<Integer>();

            while (rs.next()) {
                list.add(rs.getInt(1));
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update Subjects for the given Course.
     *
     * @param course_id   the course's id to update.
     * @param subject_ids list of subject_id to add.
     * @throws SQLException if a database access error occurs.
     */
    public void update(int course_id, List<Integer> subject_ids) throws SQLException {
        try {
            connect();

            st = con.prepareStatement(delete);
            delete(course_id);

            st = con.prepareStatement(insert);
            insert(course_id, subject_ids);
        } finally {
            close();
        }
    }

    /**
     * Delete all Subjects for the given Course.
     *
     * @param course_id user's id to delete.
     * @throws SQLException if a database access error occurs.
     */
    private void delete(int course_id) throws SQLException {
        st.setInt(1, course_id);
        st.executeUpdate();
    }

    /**
     * Insert a list of Subjects for the given Course into database.
     *
     * @param course_id   course's id to insert.
     * @param subject_ids list of subject_id to insert.
     * @throws SQLException if a database access error occurs.
     */
    private void insert(int course_id, List<Integer> subject_ids) throws SQLException {
        for (int subject_id : subject_ids) {
            st.setInt(1, course_id);
            st.setInt(2, subject_id);
            st.executeUpdate();
        }
    }
}
